import xlrd
import xlwt
import openpyxl
import sys
import xlsxwriter
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
import inspect
import os

# ws = Workbook('土地分类面积表2.xlsx')
#
# list = []  # 存储数据表QSDWMC中去重后的名称
# sarea = []  # 存储数据表Shape_Area数值
# dlmc = []  # 存储数据表DLMC名称
# qsdwmc = [] # 存储数据表QSDWMC中未去重的名称
# #linenumber = [0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
# #              30]
# #landtypes = {'水田': 2, '水浇地': 3, '旱地': 4, '果园': 5, '有林地': 6, '沟渠': 7,
# #              '道路': 8, '坑塘水面': 9, '设施农用地': 10, '田坎': 11, '仓储用地': 13,
# #              '科教用地': 14, '城市': 15, '建制镇': 16, '村庄': 17, '公路': 18, '水利设施用地': 19,
# #              '墓葬地': 20, '其他草地': 21}
# land = ['水田', '水浇地', '旱地', '果园', '有林地', '沟渠',
#         '道路', '坑塘水面', '设施农用地', '田坎', '仓储用地',
#         '科教用地', '城市', '建制镇', '村庄', '公路', '水利设施用地',
#         '墓葬地', '其他草地']
# types = [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21]    # 需要输入数据的序列
#
# types2 = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U']    # 需要输入数据的序列
#
#
# def get_variable_name(variable):
#     callers_local_vars = inspect.currentframe().f_back.f_locals.items()
#     return [var_name for var_name, var_val in callers_local_vars if var_val is variable]
#
# # 读取数据表中的数据
# def readexcel():
#     file = 'qqq11.xls'
#
#     workbook = xlrd.open_workbook(file)     # 创建工作空间
#     sheet1 = workbook.sheet_by_index(0)     # 选取第0个表
#
#     dlmclist = (sheet1.col_values(4))  # 获取第4列数据
#     dlmclist.pop(0)     # 把第一行标题弹出去
#
#     sarealist = (sheet1.col_values((13)))   # 获取第13列数据
#     sarealist.pop(0)
#
#     n1 = len(sarealist)
#     for a in range(0, n1):  # 数据添加到列表
#         sarea.append(sarealist[a])
#
#     n2 = len(dlmclist)
#     for b in range(0, n2):  # 数据添加到列表
#         dlmc.append(dlmclist[b])
#
#     # dlmclist.append(sheet1.col_values(4))
#     iplist = (sheet1.col_values(5))  # 获取第5列数据
#     iplist.pop(0)  # 把第一行标题弹出去
#     for i in iplist:
#         if i not in list:  # 去掉重复的
#             list.append(i)
#     # print(sheet1.cell_value(row,5))
#     # n3 = len(iplist)
#     for b in range(0, n2):  # 将有未去重的数据添加到列表
#         qsdwmc.append(iplist[b])
#
#
# # 在模板中写入数据
# def writeexcel():
#     file = '土地分类面积表2.xlsx'
#
#     workbook = openpyxl.load_workbook(file)     # 创建工作空间
#     sheet1 = workbook['Sheet1']     # 选取Sheet1表
#
#     # 写入第一列
#     n = len(list)
#     for i in range(0, n):
#         workbook.active.insert_rows(4 + i)  # 在第四行前插入一行，无格式
#         m = len(list[i])    # 通过数据长度修改行高
#         if m > 4:
#             sheet1.row_dimensions[4 + i].height = 30
#         if m > 8:
#             sheet1.row_dimensions[4 + i].height = 45
#         sheet1.cell(4 + i, 1).value = list[i]   # 写入单元格
#
#     #workbook.save('test1.xls')
#
#     q1 = 0
#     ld1 = 0
#     d1 = 0
#     ldlen = len(land)
#     llen = len(list)
#     qlen = len(qsdwmc)
#     for l1 in range(0, llen):   # 确定行数
#
#         for q1 in range(0, ldlen):  #
#
#             if list[l1] == qsdwmc[0]:   # 根据行标题写书行数据
#
#                 for ld1 in range(0, ldlen):     # 遍历land
#
#                     if land[ld1] == dlmc[0]:     # 判断匹配
#                         # 其他写入方法
#                         # sheet1['%s%s' % (types2[ld1],types3[l1])] = sarea[0]
#                         # sheet1.cell(row = l1 + 4,column = types[ld1],value = sarea[0])
#
#                         data = sheet1.cell(4 + l1, types[ld1]).value    # 用于判断单元格是否为空
#                         sum1 = sheet1.cell(4 + l1, 22).value    # 用于同名称数据相加
#
#                         if data is None:    # 判断是否为空
#                             sheet1.cell(4 + l1, types[ld1]).value = sarea[0]    # 空值直接写入
#                             workbook.save('test1.xlsx')
#
#                             sheet1.column_dimensions[types2[ld1]].width = 8     # 写入后调整列宽
#                             workbook.save('test1.xlsx')
#                         else:
#                             sarea2 = data + sarea[0]    # 非空值进行相加后写入
#                             sheet1.cell(4 + l1, types[ld1]).value = sarea2
#                             workbook.save('test1.xlsx')
#
#                         if sum1 is None:    # 判断合计列单元格是否为空
#                             sheet1.cell(4 + l1, 22).value = sarea[0]    # 空值直接写入
#                             workbook.save('test1.xlsx')
#                         #     sheet1.column_dimensions['V'].width = 8   # 设置列宽
#                         #     workbook.save('test1.xlsx')
#                         else:
#                             sum = sum1 + sarea[0]   # # 非空值进行相加后写入
#                             sheet1.cell(4 + l1, 22).value = sum
#                             workbook.save('test1.xlsx')
#                     else:
#                         pass
#
#                 qsdwmc.pop(0)
#                 dlmc.pop(0)
#                 sarea.pop(0)
#             elif qsdwmc[0] == '':
#                 return 0
#             else:
#
#                 break
#
#     workbook.save('test1.xlsx')
#
#
# def summation():
#     file = 'test1.xlsx'
#     workbook = xlrd.open_workbook(file)
#     sheet1 = workbook.sheet_by_index(0)
#
#     workbook2 = openpyxl.load_workbook(file)
#     sheet2 = workbook2['Sheet1']
#
#     types.append(22)
#
#     cols_sum = locals()
#     cols_list = locals()
#     for i in types:
#         cols_list['cols_list_' + str(i - 1)] = []
#         cols_list['cols_list_' + str(i - 1)] = (sheet1.col_values(i - 1))
#         cols_list['cols_list_' + str(i - 1)].pop(0)
#         cols_list['cols_list_' + str(i - 1)].pop(0)
#         cols_list['cols_list_' + str(i - 1)].pop(0)
#         # del cols_list['cols_list_' + str(i)][0:3]
#         # cols_sum['cols_sum_' + str(i - 1)] = 0
#         print(cols_list['cols_list_' + str(i - 1)])
#         print(len(cols_list['cols_list_' + str(i - 1)]))
#         lc = len(cols_list['cols_list_' + str(i - 1)])
#         for j in cols_list['cols_list_' + str(i - 1)]:
#             data = sheet2.cell(lc + 3, i).value
#             # sum2 = sheet2.cell
#             if j == '':
#                 pass
#
#             elif data is None:
#                 sheet2.cell(lc + 3, i).value = j
#
#             else:
#                 sum2 = data + j
#                 sheet2.cell(lc + 3, i).value = sum2
#                 workbook2.save('test2.xlsx')
#
#     sum_list = sheet2.cell(11, 22).value
#     print(sum_list)
#     # sum_list = (sheet1.col_values(22))
#     # print(sum_list)
#
#     # cols_sum['cols_sum_' + str(i - 1)] += j
#     # print(cols_sum['cols_sum_' + str(i - 1)])
#     # a = get_variable_name(cols_sum['cols_sum_' + str(i - 1)]).pop()
#     # print(a)
#
#     # a = get_variable_name(cols_list['cols_list_2']).pop()
#     # print(a)
#
#     # for a in range(0,len(cols1)):
#     #     sarea.append(cols1[a])

#
# if __name__ == '__main__':
#     readexcel()
#     writeexcel()
#     summation()

# def landform_region():
#     list = []  # 存储数据表QSDWMC中去重后的名称
#     file = 'qqq11.xls'
#     workbook = xlrd.open_workbook(file)     # 创建工作空间
#     sheet1 = workbook.sheet_by_index(0)     # 选取第0个表
#
#     # dlmclist = (sheet1.col_values(5))  # 获取第4列数据
#     # dlmclist.pop(0)     # 把第一行标题弹出去
#     # print(dlmclist)
#
#     # dlmclist.append(sheet1.col_values(4))
#     iplist = (sheet1.col_values(5))  # 获取第5列数据
#     iplist.pop(0)  # 把第一行标题弹出去
#     for i in iplist:
#         if i not in list:  # 去掉重复的
#             list.append(i)
#     list.pop(-1)
#     print(list)
#     ww = '、'.join(list)
#     print(ww)
#
# landform_region()


def landform_totalArea():
    eachArea = 0
    file = 'qqq1.xls'
    workbook = xlrd.open_workbook(file)  # 创建工作空间
    sheet1 = workbook.sheet_by_index(0)  # 选取第0个表
    eachArea_list = (sheet1.col_values(13))
    eachArea_list.pop(0)
    #eachArea_list.pop(-1)
    eachArea_list = [i for i in eachArea_list if i != '']

    total_nrows = len(eachArea_list)
    print(total_nrows)
    print(eachArea_list)
    for i in range(0,len(eachArea_list)):
        print(eachArea_list[i])
        if eachArea_list[i] is not None:
            eachArea = eachArea + eachArea_list[i]
            print(eachArea)
    if eachArea > 666.67:
        eachArea = eachArea/666.67
    print(eachArea_list)
    print('%s亩' % eachArea)
    print('%s平方米' % eachArea)
    if eachArea > 666.67:
        eachArea = eachArea / 666.67
        return '%s亩' % eachArea
    return '%s平方米' % eachArea

    return eachArea
landform_totalArea()